﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MxWeiXinPF.DBUtility;
using System.Data;
using MxWeiXinPF.Common;
using System.Data.SqlClient;

namespace MxWeiXinPF.DAL
{
    public partial class indextoppic
    {
        private string databaseprefix;

        public indextoppic()
        {
            this.databaseprefix = "dt_";
        }

        public indextoppic(string _databaseprefix)
        {
            this.databaseprefix = _databaseprefix;
        }

        public int Add(MxWeiXinPF.Model.indextoppic model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into " + this.databaseprefix + "indextoppic(");
            builder.Append("shopid,title,link_url,img_url,zhaiyao,content,sort_id,status,add_time,update_time,wid)");
            builder.Append(" values (");
            builder.Append("@shopid,@title,@link_url,@img_url,@zhaiyao,@content,@sort_id,@status,@add_time,@update_time,@wid)");
            builder.Append(";set @ReturnValue= @@IDENTITY");
            SqlParameter[] para = new SqlParameter[] { new SqlParameter("@shopid", SqlDbType.Int, 4), new SqlParameter("@title", SqlDbType.NVarChar, 100), new SqlParameter("@link_url", SqlDbType.NVarChar, 0xff), new SqlParameter("@img_url", SqlDbType.NVarChar, 0xff), new SqlParameter("@zhaiyao", SqlDbType.NVarChar, 0xff), new SqlParameter("@content", SqlDbType.NText), new SqlParameter("@sort_id", SqlDbType.Int, 4), new SqlParameter("@status", SqlDbType.TinyInt, 1), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@update_time", SqlDbType.DateTime), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@ReturnValue", SqlDbType.Int) };
            para[0].Value = model.shopid;
            para[1].Value = model.title;
            para[2].Value = model.link_url;
            para[3].Value = model.img_url;
            para[4].Value = model.zhaiyao;
            para[5].Value = model.content;
            para[6].Value = model.sort_id;
            para[7].Value = model.status;
            para[8].Value = model.add_time;
            para[9].Value = model.update_time;
            para[10].Value = model.wid;
            para[11].Direction = ParameterDirection.Output;
            List<CommandInfo> sQLStringList = new List<CommandInfo>();
            CommandInfo item = new CommandInfo(builder.ToString(), para);
            sQLStringList.Add(item);
            DbHelperSQL.ExecuteSqlTranWithIndentity(sQLStringList);
            return (int)para[11].Value;
        }

        public bool Delete(int id)
        {
            new StringBuilder().Append("delete from " + this.databaseprefix + "indextoppic_attribute_value ");
            List<CommandInfo> cmdList = new List<CommandInfo>();
            StringBuilder builder2 = new StringBuilder();
            builder2.Append("delete from " + this.databaseprefix + "indextoppic ");
            builder2.Append(" where id=@id");
            SqlParameter[] para = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            para[0].Value = id;
            CommandInfo item = new CommandInfo(builder2.ToString(), para);
            cmdList.Add(item);
            return (DbHelperSQL.ExecuteSqlTran(cmdList) > 0);
        }

        public bool Exists(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from " + this.databaseprefix + "indextoppic");
            builder.Append(" where id=@id ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public int GetCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(*) as H ");
            builder.Append(" from " + this.databaseprefix + "indextoppic");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return Convert.ToInt32(DbHelperSQL.GetSingle(builder.ToString()));
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" id,shopid,title,link_url,img_url,zhaiyao,content,sort_id,status,add_time,update_time,wid ");
            builder.Append(" FROM " + this.databaseprefix + "indextoppic ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * FROM " + this.databaseprefix + "indextoppic");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListTopPic(int shopid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            builder.Append(" id,shopid,title,link_url,img_url,zhaiyao,content,sort_id,status,add_time,update_time,wid ");
            builder.Append(" FROM " + this.databaseprefix + "indextoppic ");
            builder.Append(" where shopid=" + shopid);
            builder.Append(" order by sort_id");
            return DbHelperSQL.Query(builder.ToString());
        }

        public MxWeiXinPF.Model.indextoppic GetModel(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 id,shopid,title,link_url,img_url,zhaiyao,content,sort_id,status,add_time,update_time,wid");
            builder.Append(" from " + this.databaseprefix + "indextoppic ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.indextoppic indextoppic = new MxWeiXinPF.Model.indextoppic();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if (set.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    indextoppic.id = int.Parse(set.Tables[0].Rows[0]["id"].ToString());
                }
                if (set.Tables[0].Rows[0]["shopid"].ToString() != "")
                {
                    indextoppic.shopid = int.Parse(set.Tables[0].Rows[0]["shopid"].ToString());
                }
                indextoppic.title = set.Tables[0].Rows[0]["title"].ToString();
                indextoppic.link_url = set.Tables[0].Rows[0]["link_url"].ToString();
                indextoppic.img_url = set.Tables[0].Rows[0]["img_url"].ToString();
                indextoppic.zhaiyao = set.Tables[0].Rows[0]["zhaiyao"].ToString();
                indextoppic.content = set.Tables[0].Rows[0]["content"].ToString();
                if (set.Tables[0].Rows[0]["sort_id"].ToString() != "")
                {
                    indextoppic.sort_id = int.Parse(set.Tables[0].Rows[0]["sort_id"].ToString());
                }
                if (set.Tables[0].Rows[0]["status"].ToString() != "")
                {
                    indextoppic.status = int.Parse(set.Tables[0].Rows[0]["status"].ToString());
                }
                if (set.Tables[0].Rows[0]["add_time"].ToString() != "")
                {
                    indextoppic.add_time = DateTime.Parse(set.Tables[0].Rows[0]["add_time"].ToString());
                }
                if (set.Tables[0].Rows[0]["update_time"].ToString() != "")
                {
                    indextoppic.update_time = new DateTime?(DateTime.Parse(set.Tables[0].Rows[0]["update_time"].ToString()));
                }
                if (set.Tables[0].Rows[0]["wid"].ToString() != "")
                {
                    indextoppic.wid = new int?(int.Parse(set.Tables[0].Rows[0]["wid"].ToString()));
                }
                return indextoppic;
            }
            return null;
        }

        public string GetTitle(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select top 1 title from " + this.databaseprefix + "indextoppic");
            builder.Append(" where id=" + id);
            string str = Convert.ToString(DbHelperSQL.GetSingle(builder.ToString()));
            if (string.IsNullOrEmpty(str))
            {
                return "";
            }
            return str;
        }

        public DataSet GetWCodeList(int wid, int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * FROM " + this.databaseprefix + "indextoppic");
            builder.Append(" where  wid=" + wid);
            if (strWhere.Trim() != "")
            {
                builder.Append("  and  " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public bool Update(MxWeiXinPF.Model.indextoppic model)
        {
            using (SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        StringBuilder builder = new StringBuilder();
                        builder.Append("update " + this.databaseprefix + "indextoppic set ");
                        builder.Append("shopid=@shopid,");
                        builder.Append("title=@title,");
                        builder.Append("link_url=@link_url,");
                        builder.Append("img_url=@img_url,");
                        builder.Append("zhaiyao=@zhaiyao,");
                        builder.Append("content=@content,");
                        builder.Append("sort_id=@sort_id,");
                        builder.Append("status=@status,");
                        builder.Append("add_time=@add_time,");
                        builder.Append("update_time=@update_time");
                        builder.Append(" where id=@id");
                        SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@shopid", SqlDbType.Int, 4), new SqlParameter("@title", SqlDbType.NVarChar, 100), new SqlParameter("@link_url", SqlDbType.NVarChar, 0xff), new SqlParameter("@img_url", SqlDbType.NVarChar, 0xff), new SqlParameter("@zhaiyao", SqlDbType.NVarChar, 0xff), new SqlParameter("@content", SqlDbType.NText), new SqlParameter("@sort_id", SqlDbType.Int, 4), new SqlParameter("@status", SqlDbType.TinyInt, 1), new SqlParameter("@add_time", SqlDbType.DateTime), new SqlParameter("@update_time", SqlDbType.DateTime), new SqlParameter("@wid", SqlDbType.Int, 4), new SqlParameter("@id", SqlDbType.Int, 4) };
                        cmdParms[0].Value = model.shopid;
                        cmdParms[1].Value = model.title;
                        cmdParms[2].Value = model.link_url;
                        cmdParms[3].Value = model.img_url;
                        cmdParms[4].Value = model.zhaiyao;
                        cmdParms[5].Value = model.content;
                        cmdParms[6].Value = model.sort_id;
                        cmdParms[7].Value = model.status;
                        cmdParms[8].Value = model.add_time;
                        cmdParms[9].Value = model.update_time;
                        cmdParms[10].Value = model.wid;
                        cmdParms[11].Value = model.id;
                        DbHelperSQL.ExecuteSql(connection, transaction, builder.ToString(), cmdParms);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }

        public void UpdateField(int id, string strValue)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update " + this.databaseprefix + "indextoppic set " + strValue);
            builder.Append(" where id=" + id);
            DbHelperSQL.ExecuteSql(builder.ToString());
        }
    }
}
